package com.qidian.hw.dao.impl;

import com.qidian.hw.dao.NewsDAO;
import com.qidian.hw.pojo.News;
import com.qidian.hw.tools.FormatUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @Author ꒰ঌ(李ᆺ琛)໒꒱
 * @Date 2022/8/19 18:19
 * @Version 1.8
 */
public class NewsDAOImpl extends BaseDAO implements NewsDAO {
    private Connection con;
    private PreparedStatement pstmt;
    private ResultSet rs;

    @Override
    public int saveNews(News news) {
        int result = 0;
        String sql = "insert into news (news_title,news_desc,news_img,news_keyword,news_click_count,news_details, " +
                " create_time,modify_time) values (?,?,?,?,?,?,?,?) ";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, news.getNewsTitle ());
            pstmt.setString (2, news.getNewsDesc ());
            pstmt.setString (3, news.getNewsImg ());
            pstmt.setString (4, news.getNewsKeyword ());
            pstmt.setInt (5, news.getNewsClickCount ());
            pstmt.setString (6, news.getNewsDetails ());
            String dateStr = FormatUtil.getNow ();//获取当前系统时间
            pstmt.setString (7, dateStr);
            pstmt.setString (8, dateStr);
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    @Override
    public int deleteNews(long newsId) {
        int result = 0;
        String sql = "update news set status=0,modify_time=? where news_id = ?";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, FormatUtil.getNow ());
            pstmt.setLong (2, newsId);
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    @Override
    public int updateNews(News news) {
        int result = 0;
        String sql = "update news set news_title=?,news_desc=?,news_img=?,news_keyword=?,news_click_count=?, " +
                " news_details=?,modify_time=? where news_id = ? and status = 1 ";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, news.getNewsTitle ());
            pstmt.setString (2, news.getNewsDesc ());
            pstmt.setString (3, news.getNewsImg ());
            pstmt.setString (4, news.getNewsKeyword ());
            pstmt.setInt (5, news.getNewsClickCount ());
            pstmt.setString (6, news.getNewsDetails ());
            String dateStr = FormatUtil.getNow ();//获取当前系统时间
            pstmt.setString (7, dateStr);
            pstmt.setLong (8, news.getNewsId ());
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    @Override
    public ArrayList<News> listNews() {
        ArrayList<News> newss = new ArrayList<News> ();
        String sql = "select * from news where status = 1";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while (rs.next ()) {
                News news = new News ();
                news.setNewsId (rs.getLong ("news_id"));
                news.setNewsTitle (rs.getString ("news_title"));
                news.setNewsDesc (rs.getString ("news_desc"));
                news.setNewsImg (rs.getString ("news_img"));
                news.setNewsKeyword (rs.getString ("news_keyword"));
                news.setNewsClickCount (rs.getInt ("news_click_count"));
                news.setNewsDetails (rs.getString ("news_details"));
                news.setCreateTime (rs.getString ("create_time"));
                news.setModifyTime (rs.getString ("modify_time"));
                newss.add (news);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return newss;
    }

    @Override
    public News queryNewsById(long newsId) {
        News news = null;
        String sql = "select * from news where news_id = ? and status = 1";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setLong (1, newsId);
            rs = pstmt.executeQuery ();
            if (rs.next ()) {
                news = new News ();
                news.setNewsId (rs.getLong ("news_id"));
                news.setNewsTitle (rs.getString ("news_title"));
                news.setNewsDesc (rs.getString ("news_desc"));
                news.setNewsImg (rs.getString ("news_img"));
                news.setNewsKeyword (rs.getString ("news_keyword"));
                news.setNewsClickCount (rs.getInt ("news_click_count"));
                news.setNewsDetails (rs.getString ("news_details"));
                news.setCreateTime (rs.getString ("create_time"));
                news.setModifyTime (rs.getString ("modify_time"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return news;
    }

    @Override
    public int count(String title) {
        int result = 0;
        String sql = "select count(*) from news where status = 1 ";
        if (FormatUtil.isNull (title)) {
            sql += " and news_title like '%" + title + "%' ";
        }
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            if (rs.next ()) {
                result = rs.getInt (1);
            }
        } catch (Exception e) {
            e.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    @Override
    public ArrayList<News> queryByPage(int page, int size, String title) {
        ArrayList<News> newss = new ArrayList<News> ();
        String sql = "select * from news where status = 1 ";
        if (FormatUtil.isNull (title)) {
            sql += " and news_title like '%" + title + "%' ";
        }
        sql += " limit " + ((page - 1) * size) + "," + size;
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while (rs.next ()) {
                News news = new News ();
                news.setNewsId (rs.getLong ("news_id"));
                news.setNewsTitle (rs.getString ("news_title"));
                news.setNewsDesc (rs.getString ("news_desc"));
                news.setNewsImg (rs.getString ("news_img"));
                news.setNewsKeyword (rs.getString ("news_keyword"));
                news.setNewsClickCount (rs.getInt ("news_click_count"));
                news.setNewsDetails (rs.getString ("news_details"));
                news.setCreateTime (rs.getString ("create_time"));
                news.setModifyTime (rs.getString ("modify_time"));
                newss.add (news);
            }
        } catch (Exception e) {
            e.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return newss;
    }

    @Override
    public int deleteBatch(String[] ids) {
        int result = 0;
        String sql = "update news set status=0,modify_time=? where news_id in (";
        for(int i = 0; i <ids.length;i++) {
            sql += ids[i]+",";
        }
        sql += "0)";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, FormatUtil.getNow ());
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }
}
